<?php 
/* cong thức excel 
	Hiệu quả công việc = lương HĐ - Lương CB
	Ngày công hưởng lương = số ngày làm nhân viên + số ngày nghỉ hương lương
	Phu cấp di lại =  số ngày di làm thực * phu cap di lai
	Trách nhiệm theo công = (trách nhiệm/số ngày làm làm theo qui định)* công thực tế
	Tổng phụ cấp = thâm niên + đi lại theo công + trách nhiệm theo công + phụ cấp đi lại
	Lương theo ngày công = (Lương HĐ/công chuẩn) * công thực tế + (lương CB/công chuẩn) * ngày nghỉ hưởng lương
	Tổng thu nhập = tổng phu cấp + lương theo công + bu lương
	công đoàn = lương CB * 1%
	BHXH BHYT BHTN = lương CB * 9,5%;
	Tổng trừ = tạm ứng + công đoàn + BHXH BHYT BHTN + khoản trừ khác + số tien ditrevesom
	BHXH,BHYT,BHTH,KPCĐ(21%)=Lương CB *21%
	Thực lãnh = (tong thu nhap - tong khoang tru) * he so luong
*/
class export_salary extends Public_Controller{
	public $profiles;
	public $cache_profile = 'emp_profile/export_';
	function __construct(){
		parent::__construct();		
		$this->check_permissions();		
		$this->load->model("number_m");
		$this->load->model("export_salary_m","export_m");
		$this->load->helper('form');
		$this->template->set_layout('salary.html');
		$this->excel_background = "";
		$this->load->helper("function_salary");//cong thu tinh luong.
		$this->load->helper("excel");
		$this->array_symbol = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG");
	}
	function index(){
		$this->action = 'back|'.site_url('emp_profile/salary').'|'.lang('global:back');
		$this->action .= '+print run|javascript:void(0)|'.lang('salary:prinfile');
		$this->action .='+excel export_excel|javascript:void(0)|'.lang('export:export_fileluong');
		$this->action .='+excel export_excelTHBS|javascript:void(0)|'.lang('export:export_fileTHBS');
		$this->action .='+excel export_excelTHBL|javascript:void(0)|'.lang('export:export_fileTHBL');
		$this->action .='+excel export_excelbank|javascript:void(0)|'.lang('export:export_filebank');
		
		$year  	 = ($this->input->get("year")) ?$this->input->get("year"):date("Y");
		$month 	 = ($this->input->get("month"))?$this->input->get("month"):date("m");
		$id_room = ($this->input->get('id_room'))?$this->input->get("id_room"):""; 	
		$data_employ = $this->export_m->get_list_employ($year,$month,1,$id_room);
		$room = $this->export_m->get_levelroom(1);
		$array_room[""] = "Tất cả";
		$date      = $this->export_m->get_date_work($year,$month); // lay so ngay lam chuan 
		$list_idroom = $this->export_m->get_list_idroom(6); 
		if(!empty($room)) 
			foreach($room as $item) $array_room[$item->id] = $item->title; 
		$month 	= $this->number_m->month_select();
		$year 	= $this->number_m->year_select($this->year_start,date("Y"));
		if ($this->input->is_ajax_request())
		{
			$this->template->set_layout(false);
		}
		$this->template->set('admin',true)->append_css('toolbar.css');
	  	$this->template ->set("title",lang('salary:add_table_salary'))
			->set("list_employ",$data_employ)
			->set("room",$array_room)
			->set("year",$year)
			->set("month",$month)
			->set("id_room",$id_room)
			->set("date",$date)
			->set("list_idroom",$list_idroom)
			->set("action",$this->action)
			->append_css("breadcrumb.css")
			->append_css('table.css')
			->append_js('ui/jquery-ui.min.js')
			->append_js('filter.js')
			->append_css('ui/jquery-ui.min.css')
			->append_js('admin/plugins.js')
			->append_js('admin/scripts.js')
			->append_js('business/site.js')
			->append_js('chosen/chosen.jquery.min.js')
			->append_css('chosen/chosen.css');			
			$this->input->is_ajax_request() ? $this->template->build($this->controller.'/partial/list') : $this->template->build($this->controller.'/index');
	}
	// header 
	function export_excel()
	{
		$year  	 = ($this->input->get("year")) ?$this->input->get("year"):date("Y");
		$month 	 = ($this->input->get("month"))?$this->input->get("month"):date("m");
		$id_room = ($this->input->get('id_room'))?$this->input->get("id_room"):""; 
		$date      = $this->export_m->get_date_work($year,$month); // lay so ngay lam chuan 
		if(!empty($date)){
			$data_employ = $this->export_m->get_list_employ($year,$month,1,$id_room);	
			$list_idroom = $this->export_m->get_list_idroom(6); 
			$this->excel_background = "ccffcc";
			$this->load->library("excel");
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->setActiveSheetIndex(0);
			$list_title = array("STT","Họ và Tên","BP","CV","CMND","Lương CB","Hiệu Quả CV","Tổng Lương theo HĐ","Công Chuẩn","Công Thực Tế","Nghỉ hưỡng lương","Nghỉ không lương","Ngày công hưỡng lương","Thâm niên","Mức phụ cấp đi lại","Đi lại theo công","Trách nhiệm/Kiêm nghiệm","Trách nhiệm theo công","Tổng phụ cấp","Lương theo ngày công","Bù lương","Tổng thu nhập","Tạm ứng","Công đoàn","BHXH, BHYT, BHTN","Trừ khác","Tổng trừ","BHXH,BHYT,BHTH,KPCĐ(21%)","Tổng thời gian trễ/sớm (phút)","Khoản trừ đi trễ/sớm","Hệ số lương","Thực lãnh","Thực lãnh","\n");
			$symbol_width = array("A"=>10,"B"=>30,"AB"=>30); // chieu rong từng cot
			header_excel($list_title,$symbol_width,$objPHPExcel,1,true,true);
			$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);
			$row = 3;
			$row_start = $row;
			$array_symbol =$this->array_symbol;
			$row_start = 0;
			$data_employ = array_filter($data_employ);
			if(!empty($data_employ)) foreach($data_employ as $name_room => $value)
			{	
				$objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(20);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$row,$name_room);
				$objPHPExcel->getActiveSheet()->freezePane('C2');
				$styleArray = array('font' => array('bold'=> true, 'color' => array('rgb'=> '0000ff'),'size'  => 16));
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':AG'.$row)->applyFromArray($styleArray);	
				$i = 0;
				$row_start = $row;
				if(!empty($value))foreach($value as $item){$row++;$i++;
				
					if(in_array($item["room_id"],$list_idroom)) // kiem tra ngay lam viec cua ban le hay ban si
						$date_work = $date->value2;
					else
						$date_work = $date->value1;
					$salary = salary_total($item,$date_work);
					/* end tinh tong thu nhap */
					$info = json_decode($item["info"]);
					$objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(20);
					$list_data = array($i,strtoupper($item["name_full"]),strtoupper($name_room),$item["chucvu"],strtoupper($info->id_number),$item["salary"],"=H".$row."-F".$row,$item["salary2"],$date_work,$salary["date_l"],$item["date_s"],$item["date_n"],"=J".$row."+K".$row,$item["salary_add1"],$salary["mucphucap"],$salary["total_dilai"],$item["salary_add4"],"=Q".$row."/I".$row."*J".$row,"=N".$row."+P".$row."+R".$row,"=H".$row."/I".$row."*J".$row."+F".$row."/I".$row."*K".$row,$item["except1"],"=S".$row."+T".$row."+U".$row,$item["except5"],$salary["congdoan"],$salary["baohiem"],$salary["baohiem_kpcd"],$item["except4"],"=SUM(W".$row.":Z".$row.")+AD".$row,$item["minute_t"],$salary["truditrevesom"],$item["factor"],$salary["total_thuclanh"],round($salary["total_thuclanh"],-3),1);
					insert_colum($list_data,$row,0,$objPHPExcel);// insert du lieu 
					for($symbol=5;$symbol<count($array_symbol);$symbol++){
						$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getNumberFormat()->setFormatCode('#,##'); 
					}
				}
				for($symbol=5;$symbol<count($array_symbol);$symbol++){
					$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row_start)->getNumberFormat()->setFormatCode('#,##'); 
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($symbol,$row_start,"=SUM(".$array_symbol[$symbol].($row_start+1).":".$array_symbol[$symbol].($row).")");
				}
				$row++;
			}
			header('Content-Type: application/vnd.ms-excel'); 
			header('Content-Disposition: attachment;filename="danh_sach_luong_'.$month.'_'.$year.'.xls"'); 
			header('Cache-Control: max-age=0'); 
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); 
			$objWriter->save('php://output');
		}
		else{
			echo "<script>alert('Hiện không có dữ liệu cho năm và tháng này');window.location='".site_url("emp_profile/export_salary")."'</script>";	
		}
	}
	function export_excelTH($type=""){
		
		$year  	 = ($this->input->get("year")) ?$this->input->get("year"):date("Y");
		$month 	 = ($this->input->get("month"))?$this->input->get("month"):date("m");

		$id_room = ""; 
		if($type == "BL"){
			$list_room = array(2,6);
			$_file_name = "THBL";
		}
		else{
			$list_room = array(5,7,11,14);
			$_file_name = "THBS";
		}
		$date      = $this->export_m->get_date_work($year,$month); // lay so ngay lam chuan 
		$data_employ = $this->export_m->get_list_employ($year,$month,1,$id_room,$list_room);	
		if(!empty($date)){
			$list_idroom = $this->export_m->get_list_idroom(6); 
			$this->load->library("excel");
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->setActiveSheetIndex(0);
			/* config excel */
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,1,"Công ty TNHH TMDV Phúc Hải");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,2,"288 Đường 3/2, P. 12, Quận 10, TPHCM");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,3,"BẢNG TỔNG HỢP LƯƠNG THÁNG ".$month."/".$year);
			/* end config excel*/			
			$list_title = array("STT","Bộ phận","Thâm niên","Đi lại theo công","Trách nhiệm theo công","Tổng phụ cấp","Lương theo ngày công","Bù lương","Tổng thu nhập","Tạm ứng","Công đoàn","BHXH BHYT (9.5%)","Trừ khác","Tổng trừ","BHXH BHYT BHTN KPCĐ (21%)","Thực lãnh");
			$symbol_width = array("A"=>10);
			header_excel($list_title,$symbol_width,$objPHPExcel,5);
			$row = 6;
			$array_symbol = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P");
			$i = 0;
			if(!empty($data_employ)) foreach($data_employ as $name_room => $value)
			{	
				$objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(20);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,$name_room);
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':AE'.$row)->getFont()->setBold(true);	
				$row_start = $row;
				$total_thamnien=0;$total_dilai=0;$total_tn=0;$tongphucap=0;$luongtheocong=0;$total_buluong=0;$tongthunhap=0;$total_tamung=0;$congdoan=0;$baohiem=0;$total_trukhac=0;$total_khoantru=0;$baohiem_kpcd=0;$total_thuclanh=0;			
				if(!empty($value))foreach($value as $item){
					if(in_array($item["room_id"],$list_idroom))
						$date_work = $date->value2;
					else
						$date_work = $date->value1;
					$info = json_decode($item["info"]);
					$salary = salary_total($item,$date_work);
					/*list thong tin luong */
					$total_thamnien += $item["salary_add1"]; // tham nien
					$total_dilai 	+= $salary["total_dilai"];
					$total_tn		+= $salary["total_tn"]; // trách nhiệm
					$tongphucap     += $salary["tongphucap"];
					$luongtheocong  += $salary["luongtheocong"];
					$total_buluong  += $item["except1"]; 
					$tongthunhap	+= $salary["tongthunhap"];
					$total_tamung	+= $item["except5"];
					$congdoan 	    += $salary["congdoan"];
					$baohiem		+= $salary["baohiem"];
					$total_trukhac  += $item["except4"];
					$total_khoantru += $salary["total_khoantru"];
					$baohiem_kpcd	+= $salary["baohiem_kpcd"];
					$total_thuclanh += round($salary["total_thuclanh"],-3);
					/* end list thong tin luong */
				}				
				$i++;
				$list_sum = array($i,$name_room,$total_thamnien,$total_dilai,$total_tn,$tongphucap,$luongtheocong,$total_buluong,$tongthunhap,$total_tamung,$congdoan,$baohiem,$total_trukhac,$total_khoantru,$baohiem_kpcd,$total_thuclanh);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,$i);
				for($symbol=0;$symbol<count($array_symbol);$symbol++){
					$objPHPExcel->getActiveSheet()->getStyle($array_symbol[$symbol].$row)->getNumberFormat()->setFormatCode('#,##'); 
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($symbol,$row,$list_sum[$symbol]);
				}
				$row++;
			}
			header('Content-Type: application/vnd.ms-excel'); 
			header('Content-Disposition: attachment;filename="'.$_file_name.'_'.$month.'_'.$year.'.xls"'); 
			header('Cache-Control: max-age=0'); 
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); 
			$objWriter->save('php://output');
		}
		else
			echo "<script>alert('Hiện không có dữ liệu cho năm và tháng này');window.location='".site_url("emp_profile/export_salary")."'</script>";
	}
	function export_excelbank()
	{
		$this->load->helper("handle_string");
		$year  	 = ($this->input->get("year")) ?$this->input->get("year"):date("Y");
		$month 	 = ($this->input->get("month"))?$this->input->get("month"):date("m");
		$id_room = ($this->input->get('id_room'))?$this->input->get("id_room"):""; 
		$data_employ = $this->export_m->get_list_employ($year,$month,1,$id_room);	
		$date      = $this->export_m->get_date_work($year,$month); // lay so ngay lam chuan 
		if(!empty($date)){
			$list_idroom = $this->export_m->get_list_idroom(6); 
			$this->load->library("excel");
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->setActiveSheetIndex(0);
			$list_title = array("STT","So ref","so tai khoan","","","","ten nguoi huong","ten ngan hang","so tien","loai tien","noi dung");
			$symbol_width = array("A"=>5,"B"=>10,"D"=>5,"E"=>5,"F"=>5,"G"=>30,"J"=>10);
			header_excel($list_title,$symbol_width,$objPHPExcel,1,false);
			$row = 2;
			//$array_symbol = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE");
			$list_bank = array("employ"=>array(),"manager"=>array());
			$tongthunhap = 0;
			$i = 0;
			if(!empty($data_employ)) foreach($data_employ as $name_room => $value){	
				if(!empty($value))foreach($value as $item){
					if(in_array($item["room_id"],$list_idroom))
						$date_work = $date->value2;
					else
						$date_work = $date->value1;
					$info = json_decode($item["info"]);
					$name_emp = strtoupper(utf8_to_ascii($info->display_name)); 
					$salary = salary_total($item,$date_work);
					$list_emp = array(" ".$info->bank_id,"null","null","null",$name_emp,"vietcombank",round($salary["total_thuclanh"],-3),"VND","Chuyen luong thang ".$month."/".$year);
					if($info->group_id == 5) // nhan vien
						array_push($list_bank["employ"],$list_emp);
					else
						array_push($list_bank["manager"],$list_emp);			
				}
			}
			if(!empty($list_bank["manager"]))
			{
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row,"Quản lý");
				foreach($list_bank["manager"] as $key =>$value_list){
					$i++;
					$row++;
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,$i);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$row,"PHC".$i);
					$col =2;
					foreach($value_list as $key=>$value)
					{	
						$objPHPExcel->getActiveSheet()->getStyle("C".$row)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
						$objPHPExcel->getActiveSheet()->getStyle("I".$row)->getNumberFormat()->setFormatCode('#,##'); 
						if($value == "null")
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,"");
						else
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$value);
						$col++;
					}
					
				}
			}
			if(!empty($list_bank["employ"]))
			{
				$row++;
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row,"Nhân viên");
				foreach($list_bank["employ"] as $item =>$value_list){
					$i++;
					$row++;
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,$i);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$row,"PHC".$i);
					$col =2;
					foreach($value_list as $key=>$value)
					{
						$objPHPExcel->getActiveSheet()->getStyle("C".$row)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT);
						$objPHPExcel->getActiveSheet()->getStyle("I".$row)->getNumberFormat()->setFormatCode('#,##'); 
						if($value == "null")
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,"");
						else
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$value);
						$col++;
					}
				}
			}
			header('Content-Type: application/vnd.ms-excel'); 
			header('Content-Disposition: attachment;filename="dsnganhang_'.$month.'_'.$year.'.xls"'); 
			header('Cache-Control: max-age=0'); 
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); 
			$objWriter->save('php://output');
		}
		else
			echo "<script>alert('Hiện không có dữ liệu cho năm và tháng này');window.location='".site_url("emp_profile/export_salary")."'</script>";
	}
	function export_employ_noid()
	{
		$data = $this->db->select("user.id,room.title,user.display_name")->from("ser_users user")->join("emp_profile profile","profile.id = user.id")->join("emp_room room","room.id = profile.room_id")->where("id2 like","00%")->or_where("id2","")->get()->result();
		if(!empty($data)){
			echo "<style> table tr td{padding:5px;} </style><table border='1' ><tr><td>STT</td><td>ID hệ thống phần mềm</td><td><strong>Tên nhân viên</strong></td><td> <strong>Tên Phòng</strong></td></tr>";
			$i = 0;
			foreach($data as $item){
				$i++;
				echo "<tr><td>$i</td><td>".$item->id."</td><td>".$item->display_name."</td><td>".$item->title."</td></tr>";
			}
			echo "</table>";	
		}
	}	
}